--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_par_exped AS (SELECT * FROM &erp.par_exped WHERE 1=2);

DELETE FROM se_par_exped ;

 INSERT INTO se_par_exped
 (SELECT  id exped_cod,
          Decode(id,'60','HEPPNER','61','HEPPNER','62','HEPPNER','63','HEPPNER',NULL) partenaire_cod,
          NULL prfcommunic_cod,
          0 enreg_id,
          0 adresse_id,
          NULL expedcateg_cod,
          NULL edibaseval_cod,
          'N' enreg_s,
          libelle exped_lib,
          NULL exped_lib2,
          'T' tiers_typ,
          Decode(typtar,'ST',0,'TP',1,0) tariftrp_typ,
          NULL jourliv_nb,
          NULL edisoc_no,
          Decode(id,'60',1,'61',1,'62',1,'63',1,null) ediregroup_typ,
          Decode(id,'60',1,'61',1,'62',1,'63',1,null) ediexpediteur_typ,
          Decode(id,'60',3,'61',3,'62',3,'63',3,null) edicateg_typ,
          ATT25 critlibre1_val,
          ATT26 critlibre2_val,
          ATT27 critlibre3_val,
          ATT30 critlibre4_val,
          Decode(LIBRE,'O',1,LIBRE) critlibre5_val,
          ATT31 critlibre6_val,
          ATT24 critlibre7_val,
          ATT28 critlibre8_val,
          ATT29 critlibre9_val,
          NULL critlibre10_val,
          NULL critlibre11_val,
          NULL critlibre12_val,
          NULL critlibre13_val,
          NULL critlibre14_val,
          NULL critlibre15_val

   FROM uv_cexpeditions ) ;

 UPDATE se_par_exped tf SET enreg_id=(select enreg_id FROM &erp.par_exped f WHERE f.exped_cod=tf.exped_cod) ;
 UPDATE se_par_exped tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0 ;

 UPDATE se_par_exped tf SET adresse_id=(select adresse_id FROM &erp.par_exped f WHERE f.exped_cod=tf.exped_cod) ;




 CALL uvse_synchroverserp('exped_cod','PAR_EXPED','O') ;

 commit;


select * from (
SELECT &outils.stragg(source) source, exped_cod, partenaire_cod, prfcommunic_cod, enreg_id, adresse_id, expedcateg_cod, edibaseval_cod, enreg_s, exped_lib, exped_lib2, tiers_typ, tariftrp_typ, jourliv_nb, edisoc_no, ediregroup_typ, ediexpediteur_typ, edicateg_typ, critlibre1_val, critlibre2_val, critlibre3_val, critlibre4_val, critlibre5_val, critlibre6_val, critlibre7_val, critlibre8_val, critlibre9_val, critlibre10_val, critlibre11_val, critlibre12_val, critlibre13_val, critlibre14_val, critlibre15_val
FROM (
  SELECT 'uv' source ,g.* FROM se_par_exped g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.par_exped g
  )
GROUP BY exped_cod, partenaire_cod, prfcommunic_cod, enreg_id, adresse_id, expedcateg_cod, edibaseval_cod, enreg_s, exped_lib, exped_lib2, tiers_typ, tariftrp_typ, jourliv_nb, edisoc_no, ediregroup_typ, ediexpediteur_typ, edicateg_typ, critlibre1_val, critlibre2_val, critlibre3_val, critlibre4_val, critlibre5_val, critlibre6_val, critlibre7_val, critlibre8_val, critlibre9_val, critlibre10_val, critlibre11_val, critlibre12_val, critlibre13_val, critlibre14_val, critlibre15_val
HAVING Count(1)!=2) r
ORDER BY exped_cod;